insert overwrite table jms_dm.dm_traffic_flow_details_dt partition(dt)
select detail.taking_date as inputtime  --日期
       ,detail.waybill_no  --运单号
       ,waybill.goods_name  --物品名称
       ,detail.taking_time as collect_time --上门取件时间
       ,nvl(detail.start_regional_id,'--') as send_regional_id  --始发大区id
       ,nvl(detail.start_regional_desc,'--') as send_regional  --始发大区name
       ,nvl(detail.start_subordinate_agent_code,'--') as send_agent_code  -- 始发代理区code
       ,nvl(detail.start_subordinate_agent_name,'--') as send_agent  -- 始发代理区name
       ,nvl(detail.start_parent_network_code,'--') as send_fran_code  -- 始发加盟商code
       ,nvl(detail.start_parent_network_name,'--') as send_fran  -- 始发加盟商name
       ,nvl(detail.start_provider_id,'--') as send_province_id  -- 始发省份id
       ,nvl(detail.start_provider_desc,'--') as send_province  -- 始发省份name
       ,nvl(detail.start_city_id,'--') as send_city_id  -- 始发城市id
       ,nvl(detail.start_city_desc,'--') as send_city  -- 始发城市name
       ,nvl(detail.start_area_id,'--') as send_area_id  -- 始发区id
       ,nvl(detail.start_area_desc,'--') as send_area  -- 始发区name
       ,case when distributi.code is not null then distributi.code else network_distributi.center_code end as send_distributecode  -- 始发转运中心code
       ,case when distributi.name is not null then distributi.name else network_distributi.center_name end as send_distribute      -- 始发转运中心name
       ,nvl(network_distributi.entrepot_code,'--') as entrepot_code      -- 始发集散点code
       ,nvl(network_distributi.entrepot_name,'--') as entrepot_name      -- 始发集散点name
       ,nvl(detail.start_pick_network_code,'--') as send_network_code  -- 始发网点code
       ,nvl(detail.start_pick_network_name,'--') as send_network  -- 始发网点name
       ,nvl(detail.sign_network_code,'--') as receiver_network_code  -- 签收网点code
       ,nvl(detail.sign_network_name,'--') as receiver_network  -- 签收网点name
       ,nvl(detail.sign_subordinate_agent_code,'--') as receiver_agent_code  -- 签收代理区code
       ,nvl(detail.sign_subordinate_agent_name,'--') as receiver_agent  -- 签收代理区name
       ,nvl(detail.sign_parent_network_code,'--') as receiver_fran_code  -- 签收加盟商code
       ,nvl(detail.sign_parent_network_name,'--') as receiver_fran  -- 签收加盟商name
       ,nvl(detail.sign_regional_id,'--') as receiver_regional_id  -- 签收大区id
       ,nvl(detail.sign_regional_desc,'--') as receiver_regional  -- 签收大区name
       ,nvl(detail.sign_provider_id,'--') as receiver_province_id -- 签收省份id
       ,nvl(detail.sign_provider_desc,'--') as receiver_province  -- 签收省份name
       ,nvl(detail.sign_city_id,'--') as receiver_city_id  -- 签收城市id
       ,nvl(detail.sign_city_desc,'--') as receiver_city  -- 签收城市name
       ,case when center_network.is_entrepot<>1 then first_code.center_code
             when center_network.is_entrepot=1 then center_network_expand.out_distribution_code else '--' end as receiver_distributecode  --目的转运中心code
       ,case when center_network.is_entrepot<>1 then first_code.center_name
             when center_network.is_entrepot=1 then center_network_expand.out_distribution_name else '无三段码' end as receiver_distribute  --目的转运中心name
       ,case when center_network.is_entrepot=1 then first_code.center_code else '--' end as dispatch_network_code  --目的集散点code
       ,case when center_network.is_entrepot=1 then first_code.center_name else '--' end as dispatch_network_name  --目的集散点name
       ,nvl(detail.sign_area_id,'--') as receiver_area_id  -- 签收区id
       ,nvl(detail.sign_area_desc,'--') as receiver_area  -- 签收区name
       ,waybill.package_inside_charge_weight as package_weight  --重量
       ,waybill.package_total_volume as package_volume -- 体积
       ,from_unixtime(unix_timestamp(detail.taking_date),'yyyy-MM-dd') as dt
  from
  (select taking_date  --日期
       ,waybill_no  --运单号
       ,taking_time  --上门取件时间
       ,start_regional_id  --始发大区id
       ,start_regional_desc  --始发大区name
       ,start_subordinate_agent_code  -- 始发代理区code
       ,start_subordinate_agent_name  -- 始发代理区name
       ,start_parent_network_code  -- 始发加盟商code
       ,start_parent_network_name  -- 始发加盟商name
       ,start_provider_id  -- 始发省份id
       ,start_provider_desc  -- 始发省份name
       ,start_city_id  -- 始发城市id
       ,start_city_desc  -- 始发城市name
       ,start_area_id  -- 始发区id
       ,start_area_desc  -- 始发区name
       ,start_center_code  -- 始发转运中心code
       ,start_center_name  -- 始发转运中心name
       ,start_pick_network_code  -- 始发网点code
       ,start_pick_network_name  -- 始发网点name
       ,sign_network_code  -- 签收网点code
       ,sign_network_name  -- 签收网点name
       ,sign_subordinate_agent_code  -- 签收代理区code
       ,sign_subordinate_agent_name  -- 签收代理区name
       ,sign_parent_network_code  -- 签收加盟商code
       ,sign_parent_network_name  -- 签收加盟商name
       ,sign_regional_id  -- 签收大区id
       ,sign_regional_desc  -- 签收大区name
       ,sign_provider_id  -- 签收省份id
       ,sign_provider_desc  -- 签收省份name
       ,sign_city_id  -- 签收城市id
       ,sign_city_desc  -- 签收城市name
       ,aim_center_code  -- 目的转运中心code
       ,aim_center_name  -- 目的转运中心name
       ,sign_area_id  -- 签收区id
       ,sign_area_desc  -- 签收区name
  from jms_dm.dm_waybill_prescription_reach_details_dt where dt>='{{ execution_date | date_add(-6) | cst_ds }}' and dt<='{{ execution_date | cst_ds }}') detail
  left join jms_dim.dim_lmdm_sys_network network
  on detail.start_pick_network_code=network.code
  left join (
          select waybill_no  -- 运单号
               ,first_code as terminal_dispatch_first_code  -- 一段码
               ,goods_name  --物品名称
               ,package_inside_charge_weight  --重量
               ,package_total_volume  -- 体积
          from jms_dwd.dwd_yl_oms_oms_waybill_dt
         where dt between date_format(date_add('{{ execution_date | date_add(-6) | cst_ds }}' ,-7), 'yyyy-MM-dd')  and '{{ execution_date | cst_ds }}') waybill on detail.waybill_no=waybill.waybill_no
left join
(
    select network_id,id,name,code from
    (
        select
            network_id,id,name,code,row_number() over (partition by network_id order by id) rw_mark
        from jms_dim.dim_lmdm_sys_network_distributi
        where distribution_type = 2
    ) tec where rw_mark=1
)distributi on network.id=distributi.network_id
left join
(
    --集散点的可交货转运中心
    select network_id,id,entrepot_code,entrepot_name,center_code,center_name from
    (
        select
            t1.network_id       as network_id
            ,t1.id              as id
            ,t1.code            as entrepot_code
            ,t1.name            as entrepot_name
            ,t2.code            as center_code
            ,t2.name            as center_name
            ,row_number() over (partition by t1.network_id order by t1.id) rw_mark
        from
        (
            select * from jms_dim.dim_lmdm_sys_network_distributi where distribution_type = 3
        ) t1
        left join
        (
            select * from jms_dim.dim_lmdm_sys_network_distributi where distribution_type = 2
        ) t2 on t1.id=t2.network_id
    ) tek where rw_mark=1
)network_distributi on network.id=network_distributi.network_id
left join (-- 一段码取目的中心
     select distinct code,center_code ,center_name from jms_dim.dim_lmdm_sys_first_code where is_enable=1 and is_delete=1) first_code on waybill.terminal_dispatch_first_code=first_code.code
left join jms_dim.dim_lmdm_sys_network center_network on first_code.center_code = center_network.code
left join jms_dim.dim_lmdm_sys_network_expand center_network_expand on first_code.center_code=center_network_expand.code ;
